'use strict';
const Service = require('egg').Service;

class VehicleStatisticsService extends Service {
  async getVehicleStatisticsByDateRange(params) {
    const {
      startDay,
      endDay
    } = params;

    const sql = `
            SELECT 
                v.vehicle_number,
                v.vehicle_type,
                v.brand_model,
                v.registration_date,
                v.power_type,
                v.displacement,
                -- 时间段末路码表显示总里程数(公里)
                (SELECT end_km FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date = ? ORDER BY record_date DESC LIMIT 1) AS end_km,
                -- 时间段行驶里程 (公里)
                (SELECT IFNULL(SUM(end_km - start_km), 0) FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date BETWEEN ? AND ?) AS mileage,
                -- 时间段出车次数 (次）
                (SELECT COUNT(DISTINCT va.application_id) FROM ov_vehicle_allocation ova
                 JOIN ov_vehicle_application va ON ova.application_id = va.application_id
                 WHERE ova.vehicle_id = v.vehicle_id AND va.status = '已批准' AND va.departure_date BETWEEN ? AND ?) AS departure_times,
                -- 时间段乘坐人次(不含司机)
                (SELECT IFNULL(SUM(passenger_count), 0) FROM (
                    SELECT 
                        ova.vehicle_id,
                        va.application_id,
                        COUNT(ap.passenger_id) AS passenger_count
                    FROM 
                        ov_application_passenger ap
                        JOIN ov_vehicle_allocation ova ON ap.application_id = ova.application_id
                        JOIN ov_vehicle_application va ON ova.application_id = va.application_id
                    WHERE 
                        va.status = '已批准'
                        AND va.departure_date BETWEEN? AND?
                    GROUP BY 
                        ova.vehicle_id, va.application_id
                ) AS subquery
                WHERE subquery.vehicle_id = v.vehicle_id) AS passenger_count,
                -- 燃油费
                (SELECT IFNULL(SUM(fuel_cost), 0) FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date BETWEEN ? AND ?) AS fuel_cost,
                -- 充电费
                (SELECT IFNULL(SUM(charge_cost), 0) FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date BETWEEN ? AND ?) AS charge_cost,
                -- 维修保养费
                (SELECT IFNULL(SUM(repair_cost), 0) FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date BETWEEN ? AND ?) AS repair_cost,
                -- 过路过桥费
                (SELECT IFNULL(SUM(roads_cost), 0) FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date BETWEEN ? AND ?) AS roads_cost,
                -- 合计
                (SELECT IFNULL(SUM(fuel_cost + charge_cost + repair_cost + roads_cost + other_cost), 0) 
                 FROM ov_driver_vehicle_daily_record WHERE vehicle_id = v.vehicle_id AND record_date BETWEEN ? AND ?) AS total_cost
            FROM 
                ov_vehicle v
            GROUP BY 
                v.vehicle_id;
        `;

    const queryParams = [
      endDay,
      startDay, endDay,
      startDay, endDay,
      startDay, endDay,
      startDay, endDay,
      startDay, endDay,
      startDay, endDay,
      startDay, endDay,
      startDay, endDay,
    ];

    try {
      return await this.app.mysql.query(sql, queryParams);
    } catch (error) {
      console.error('SQL查询出错:', error);
      // 打印完整的SQL语句用于调试
      console.log('执行的SQL语句:', this.app.mysql.format(sql, queryParams));
      throw error;
    }
  }
}

module.exports = VehicleStatisticsService;